﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Clinica_Frba.Listados_Estadisticos.queries
{
    class AfiliadosQueUtilizaronBonosQueNoCompraron : Query
    {
        private static string AfiliadosQueUtilizaronBonosQueNoCompraronQuery = "SELECT TOP 5 * FROM USUARIO;";

        public AfiliadosQueUtilizaronBonosQueNoCompraron()
            : base
                ("Afiliados que utilizaron bonos que no compraron", AfiliadosQueUtilizaronBonosQueNoCompraronQuery, null)
        {
        }

        override public String SqlQuery(int semestre, int year)
        {
            if (semestre == 1)
            {
                return " SELECT TOP 10 R.ID_AFIL Afiliado, SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) BETWEEN 1 AND 6 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) Usos , " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 1 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_1, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 2 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_2, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 3 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_3, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 4 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_4, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 5 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_5, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 6 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_6 " +
" FROM RECETA R LEFT JOIN BONO_FARMACIA B ON R.ID_RECETA = B.ID_RECETA LEFT JOIN BONO_COMPRA BC ON B.ID_BONO = BC.ID_BONO_F LEFT JOIN COMPRA C ON BC.ID_COMPRA = C.ID_COMPRA " +
//" WHERE  " +
//" R.ID_AFIL IS NOT NULL " + 
//" AND R.ID_AFIL <> C.ID_AFIL  " +
//" and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year +
//" and DATEPART ( MONTH , B.FECHA_PRESCRIPCION) <= 6 " +
" GROUP BY R.ID_AFIL " +
" ORDER BY Usos DESC ";
            }
            else
            {
                return " SELECT TOP 10 R.ID_AFIL Afiliado, SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) BETWEEN 7 AND 12 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) Usos , " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 7 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_7, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 8 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_8, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 9 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_9, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 10 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_10, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 11 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_11, " +
" SUM(CASE WHEN C.ID_AFIL IS NOT NULL AND R.ID_AFIL IS NOT NULL AND LEFT(R.ID_AFIL, 7) = LEFT(C.ID_AFIL, 7) AND R.ID_AFIL <> C.ID_AFIL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 12 and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year + " THEN 1 ELSE 0 END) as mes_12 " +
" FROM RECETA R LEFT JOIN BONO_FARMACIA B ON R.ID_RECETA = B.ID_RECETA LEFT JOIN BONO_COMPRA BC ON B.ID_BONO = BC.ID_BONO_F LEFT JOIN COMPRA C ON BC.ID_COMPRA = C.ID_COMPRA " +
//" WHERE  " +
//" R.ID_AFIL IS NOT NULL " +
//" AND R.ID_AFIL <> C.ID_AFIL  " +
//" and DATEPART ( YEAR , B.FECHA_PRESCRIPCION) = " + year +
//" and DATEPART ( MONTH , B.FECHA_PRESCRIPCION) > 6 " +
" GROUP BY R.ID_AFIL " +
" ORDER BY Usos DESC ";
            }
        }


    }
}
